package com.smart.common.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.smart.common.domain.BaseEntity;
import com.smart.common.util.Page;
import com.smart.common.util.PageImpl;
import com.smart.common.util.PageRequest;
import com.smart.common.util.QueryObj;
import com.smart.tools.ObjectHelper;

public class SqlQueryDao  <E extends BaseEntity> extends AbstractDao<E> {

	Logger logger = LoggerFactory.getLogger(AbstractDao.class);
	
	
	/**
	 * 查询拼接结果的sql/hql
	 */
	public static final String QUERY_SQL = "sql";
	
	/**
	 * 查询拼接结果的参数集合数组
	 */
	public static final String QUERY_PARAM = "obs";

	/**
	 * 列表查询SQL公共实现
	 * @author yangY
	 * @param pageRequest 分页参数
	 * @param param 参数集合
	 * @param _sql 查询基础SQL
	 * @param _extendSql 查询扩展SQL
	 * @return SQL查询的列表数据page对象
	 */
	@SuppressWarnings("unchecked")
	protected Page<Map<String,String>> getListBySql(PageRequest pageRequest, List<QueryObj> param, StringBuffer _sql, StringBuffer _extendSql){
		
		String searchSql = this.getSearchSql(param, _sql, _extendSql);
		
		logger.info("----------这是运行的sql语句啊------------["+searchSql+"]");
		try{
			logger.info("--------------这是执行的参数----------------------" +ObjectHelper.objectToJson(param));
		}catch(Exception e){
			logger.error("打印查询条件错误" ,e );
		}
		Object[] params = (Object[]) this.generateSearchConditionSql(param).get(QUERY_PARAM);
		List<Map<String,String>> result = (List<Map<String,String>>)this.executeSQLMapQuery(searchSql, params, (pageRequest.getPage()-1)*pageRequest.getRows(), pageRequest.getRows());
		
		Page<Map<String,String>> pager = new PageImpl<Map<String,String>>(pageRequest);
		pager.setTotalRows(this.getSqlCountAll(param, _sql, _extendSql));
		pager.setRecords(result);
		return pager;
	}
	
	/**
     * 列表查询SQL公共实现
     * @author yangY
     * @param pageRequest 分页参数
     * @param param 参数集合
     * @param _sql 查询基础SQL
     * @param _extendSql 查询扩展SQL
     * @return SQL查询的列表数据page对象
     */
    @SuppressWarnings("unchecked")
    protected Page<Map<String,Object>> getListObjectySql(PageRequest pageRequest, List<QueryObj> param, StringBuffer _sql, StringBuffer _extendSql){
        
        String searchSql = this.getSearchSql(param, _sql, _extendSql);
        
        logger.info("----------这是运行的sql语句啊------------["+searchSql+"]");
        Object[] params = (Object[]) this.generateSearchConditionSql(param).get(QUERY_PARAM);
        List<Map<String,Object>> result = (List<Map<String,Object>>)this.executeSQLMapQuery(searchSql, params, (pageRequest.getPage()-1)*pageRequest.getRows(), pageRequest.getRows());
        
        Page<Map<String,Object>> pager = new PageImpl<Map<String,Object>>(pageRequest);
        pager.setTotalRows(this.getSqlCountAll(param, _sql, _extendSql));
        pager.setRecords(result);
        return pager;
    }
    
	/**
	 * 列表查询(添加 额外的参数-注意参数的顺序)
	 * @param pageRequest
	 * @param param
	 * @param _sql
	 * @param _extendSql
	 * @param otherParams
	 * @return
	 * @author shutao
	 */
	@SuppressWarnings("unchecked")
	protected Page<Map<String,String>> getListBySql( PageRequest pageRequest, List<QueryObj> param, StringBuffer _sql, StringBuffer _extendSql, List<Object> otherParams ){
		String searchSql = this.getSearchSql( param, _sql, _extendSql );
		
		Object[] params = (Object[]) this.generateSearchConditionSql(param).get(QUERY_PARAM);
		Object[] newParams = new Object[ otherParams.size() + params.length ];
		int temp1 = 0;
		int temp2 = 0;
		for( int i = 0;i < newParams.length; i ++ ) {
			if( i <= otherParams.size() - 1) {
				newParams[i] = otherParams.get( temp1 ++ );
			} else {
				newParams[i] = params[ temp2 ++ ];
			}
		}
		
		String sql = " select count(*) countNum from ( " + searchSql + " ) as temp ";
		Map<String,Object> map = (Map<String,Object>)super.executeSQLAliasMapQuery( sql, newParams ).get( 0 );
		
		List<Map<String,String>> result = (List<Map<String,String>>)this.executeSQLMapQuery( searchSql, newParams, ( pageRequest.getPage() - 1 ) * pageRequest.getRows(), pageRequest.getRows() );
		Page<Map<String,String>> pager = new PageImpl<Map<String,String>>( pageRequest );
		pager.setTotalRows( Long.parseLong( map.get( "countNum" ).toString() ) );
		pager.setRecords( result );
		return pager;
	}
	/**
	 * 列表(不带分页)查询SQL公共实现
	 * @author yangY
	 * @param param 参数
	 * @param _sql 查询基础SQL
	 * @param _extendSql 扩展SQL
	 * @return 列表数据
	 */
	@SuppressWarnings("unchecked")
	protected Page<Map<String,String>> getAllListBySql(List<QueryObj> param, StringBuffer _sql, StringBuffer _extendSql){
		
		String searchSql = this.getSearchSql(param, _sql, _extendSql);
		
		Object[] params = (Object[]) this.generateSearchConditionSql(param).get(QUERY_PARAM);
		List<Map<String,String>> result = (List<Map<String,String>>)this.executeSQLMapQuery(searchSql, params, -1, -1);
		
		PageRequest pageRequest = new PageRequest();
		pageRequest.setPage(1);
		pageRequest.setRows(result.size());
		
		Page<Map<String,String>> pager = new PageImpl<Map<String,String>>(pageRequest);
		pager.setTotalRows((long)result.size());
		pager.setRecords(result);
		return pager;
	}
	
	/**
	 * 构造SQL查询列表的查询SQL
	 * @author yangY
	 * @param param 参数集合
	 * @param _sql 查询基础SQL
	 * @param _extendSql 查询扩展SQL
	 * @return 构造的查询SQL
	 */
	protected String getSearchSql(List<QueryObj> param, StringBuffer _sql, StringBuffer _extendSql){
		
		String conditionSql = this.generateSearchConditionSql(param).get(QUERY_SQL).toString();
		return _sql.toString()+conditionSql+_extendSql.toString();
	}
	
	/**
	 * 查询列表数据总数
	 * @author yangY
	 * @param param 参数集合
	 * @param _sql 基础SQL
	 * @param _extendSql 扩展SQL
	 * @return 总数
	 */
	@SuppressWarnings("unchecked")
	protected Long getSqlCountAll(List<QueryObj> param, StringBuffer _sql, StringBuffer _extendSql){
		
		String searchSql = this.getSearchSql(param, _sql, _extendSql);
		Object[] params = (Object[]) this.generateSearchConditionSql(param).get(QUERY_PARAM);
		String sql = " select count(*) countNum from ( " + searchSql + " ) as temp ";
		Map<String,Object> map = (Map<String,Object>)super.executeSQLAliasMapQuery(sql, params).get(0);
		return Long.parseLong(map.get("countNum").toString());
	}
	
	/**
	 * 执行无参SQL  返回Map对象
	 * @param sql
	 * @param param
	 * @return
	 */
	protected List<?> executeSQLMapQuery(String sql) {
		Query query = null;
		query = this.getSession().createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}
	
	
	/**
	 * 构造列表查询条件SQL/HQL
	 * @author zhangpan
	 * @param list 参数对象列表
	 * @return map (key="sql":查询SQL,key="obs":参数集合)
	 */
	protected Map<String,Object> generateSearchConditionSql(List<QueryObj> list){
		
		Map<String,Object> map = new HashMap<String, Object>();
		StringBuffer hql = new StringBuffer(100);
		int len = list.size();
		for (QueryObj queryObj : list){
			if(null!=queryObj.getQueryList())
				len = len+queryObj.getQueryList().size()-1;
		}
		
		Object[] obs = new Object[len];
		Integer obssize = 0;
		for (QueryObj queryObj : list) {
			hql.append(" AND ");
			// 复选处理
			if(null!=queryObj.getQueryList()&&queryObj.getQueryList().size()>0){
					hql.append("(");
					Integer i = obssize;
					for (QueryObj queryo : queryObj.getQueryList()) {
						if (i-obssize>0){
						   hql.append(" OR ");
						}
						hql.append(queryo.getObj() + ".");
						hql.append(queryo.getElement()).append(" ");
						hql.append(queryo.getOperator());
						hql.append(" ? ");
						obs[i] = queryo.getValue();
						i++;
					}
					obssize = i;
					hql.append(")");
				continue;
			}
			hql.append(queryObj.getObj() + ".");
			hql.append(queryObj.getElement()).append(" ");
			hql.append(queryObj.getOperator());
			hql.append(" ? ");
			obs[obssize] = queryObj.getValue();
			obssize++;
		}
		
		map.put(QUERY_SQL, hql.toString());
		map.put(QUERY_PARAM, obs);
		return map;
	}
	
	/**
	 * 列表查询(添加 额外的参数-注意参数的顺序)
	 * @param pageRequest
	 * @param param
	 * @param _sql
	 * @param _extendSql
	 * @param otherParams
	 * @return
	 * @author xuhf
	 */
	@SuppressWarnings("unchecked")
	protected Page<Map<String,Object>> getPageDataBySql( PageRequest pageRequest, List<QueryObj> param, StringBuffer _sql, StringBuffer _extendSql, List<Object> otherParams ){
		String searchSql = this.getSearchSql( param, _sql, _extendSql );
		
		Object[] params = (Object[]) this.generateSearchConditionSql(param).get(QUERY_PARAM);
		Object[] newParams = new Object[ otherParams.size() + params.length ];
		int temp1 = 0;
		int temp2 = 0;
		for( int i = 0;i < newParams.length; i ++ ) {
			if( i <= otherParams.size() - 1) {
				newParams[i] = otherParams.get( temp1 ++ );
			} else {
				newParams[i] = params[ temp2 ++ ];
			}
		}
		
		String sql = " select count(*) countNum from ( " + searchSql + " ) as temp ";
		Map<String,Object> map = (Map<String,Object>)super.executeSQLAliasMapQuery( sql, newParams ).get( 0 );
		
		List<Map<String,Object>> result = (List<Map<String,Object>>)this.executeSQLMapQuery( searchSql, newParams, ( pageRequest.getPage() - 1 ) * pageRequest.getRows(), pageRequest.getRows() );
		Page<Map<String,Object>> pager = new PageImpl<Map<String,Object>>( pageRequest );
		pager.setTotalRows( Long.parseLong( map.get( "countNum" ).toString() ) );
		pager.setRecords( result );
		return pager;
	}
	/**
	 * 列表(不带分页)查询SQL公共实现
	 * @author yangY
	 * @param param 参数
	 * @param _sql 查询基础SQL
	 * @param _extendSql 扩展SQL
	 * @return 列表数据
	 * @author xuhf
	 */
	@SuppressWarnings("unchecked")
	protected Page<Map<String,Object>> getAllDataBySql(List<QueryObj> param, StringBuffer _sql, StringBuffer _extendSql){
		
		String searchSql = this.getSearchSql(param, _sql, _extendSql);
		
		Object[] params = (Object[]) this.generateSearchConditionSql(param).get(QUERY_PARAM);
		List<Map<String,Object>> result = (List<Map<String,Object>>)this.executeSQLMapQuery(searchSql, params, -1, -1);
		
		PageRequest pageRequest = new PageRequest();
		pageRequest.setPage(1);
		pageRequest.setRows(result.size());
		
		Page<Map<String,Object>> pager = new PageImpl<Map<String,Object>>(pageRequest);
		pager.setTotalRows((long)result.size());
		pager.setRecords(result);
		return pager;
	}
	
	/**
     * 列表查询SQL公共实现 返回List
     * @author yangY
     * @param pageRequest 分页参数
     * @param param 参数集合
     * @param _sql 查询基础SQL
     * @param _extendSql 查询扩展SQL
     * @return SQL查询的列表数据page对象
     */
    @SuppressWarnings("unchecked")
    protected List<Map<String,Object>> getListObjectySql(List<QueryObj> param, StringBuffer _sql, StringBuffer _extendSql){
        
        String searchSql = this.getSearchSql(param, _sql, _extendSql);
        
        logger.info("----------这是运行的sql语句啊------------["+searchSql+"]");
        Object[] params = (Object[]) this.generateSearchConditionSql(param).get(QUERY_PARAM);
        List<Map<String,Object>> result = (List<Map<String,Object>>)this.executeSQLMapQuery(searchSql, params, -1, -1);
        return result;
    }
}
